#!/usr/bin/env python
# coding=utf-8

# File Name: pushData.py
# Author: Bill Zhang
# Mail: zchcandid@gmail.com 
# Created Time: Mon 28 Oct 2013 11:50:59 AM CST
import urllib.request  
import urllib.error
import hashlib
import json 
import time
#mysql connector
import mysql.connector
from mysql.connector import errorcode

def Run(times,interval,Dbase):
    password="ocs~rsh,uBZN"
    SessionKey=getSession(password)
    if(SessionKey==404):
        print("can't get the SessionKey\n")
        return 404
    pushURL='http://192.168.100.201:9998/Update/json/'+SessionKey

    #Fetch the data from mysql
    #get the cursor to manipulate the mysql
    db=MysqlConnect(Dbase)
    Mydb1=db.cursor(buffered=True)
    Mydb2=db.cursor(buffered=True)
    #query the data
    #Interval value of each pushing
    oits=0
    PrePush=''
    Query="SELECT id,goods_name,provider_name,warehouse,goods_number,shop_price,goods_desc,card_name,Encap,Package,SPQ,MOQ,is_rohs,cat_id,goods_sn FROM ecs_goods LIMIT "+str((times-1)*interval)+','+str(interval)
    Mydb1.execute(Query)
    #get the data from mysql and push into json

    for oits in Mydb1:
    #The data of OITS Tables
        #checke the oits
        if not oits:
            return 0
        #escape sequence
        if(oits[6]):
            IDesc=(str(oits[6]).replace("\n",""))
            IDesc=IDesc.replace("'","&#039")
            IDesc=IDesc[0:100]
        else:
            IDesc='-'
        #Encap and Pachage:
        Encap=oits[8]
        if(Encap):
            Encap=str(Encap).replace('"','\"')
            Encap=str(Encap).replace("'",'\'')
            Encap=Encap[0:50]
        else:
            Encap="-"
        Package=oits[9]   
        if(Package):

            Package=str(Package).replace('"','\'')
            Package=str(Package).replace("'",'\'')
            Package=Package[0:50]
        else:
             Package="-"
        #rohs,
        if(oits[12]==0):
            rohs="N"
        elif(oits[12]==1):
            rohs="Y"
        else:
            rohs="-"
        #price
        price=float(oits[5])
       #goods_sn
        goods_sn=oits[14]
       # goods_sn=oits[1]
        oitsData={ "HQID":oits[0],"SupplierCategory":oits[13],"SuppliersItemCode":goods_sn,"ModelCode":oits[1],"BrandName":oits[2],"Warehouse":oits[3],"Quantity":oits[4],"Price":price,"ItemDesc":IDesc,"CardName":oits[7],"Encap":Encap,"Package":Package,"SPQ":oits[10],"Rohs":rohs,"RePrice":"Y","ShowPrice":"Y","PriceParity":"Y"}
        
        oitsData=str(oitsData).replace('}',',')
        oitsData=oitsData.replace('\\','\\\ ')
        oitsData=oitsData.replace('"','\\"')
        oitsData=oitsData.replace("&#039","\\'")
    #The data of OITS1 Tables
        Ftable="ecs_goods_price_"+str(oits[0]%10)
        Query2="SELECT purchases,unit_price FROM "+Ftable+" WHERE goods_id ="+str(oits[0])
        #limit2=Ftable
        Mydb2.execute(Query2)
        MOQ=oits[11]
        i=1
        oits1=Mydb2.fetchone()
        Items='"Items":['
        if oits1 is not None:
            while oits1 is not None:
                Item={"LineNum":i,"Quantity":oits1[0],"Price":float(oits1[1])}
                if(i!=1):
                    Items =Items+","
                if MOQ==0 and i==1:
                    MOQ=oits1[0]
                Items=Items+str(Item)
                i=i+1
                oits1=Mydb2.fetchone()
            #   print(Item)
            Items=Items+'],"MOQ":'+str(MOQ)+'},'
        else:
           #if price is null the formate is "Items":[]
           # NoPrice=NoPrice+1
            Items=Items+'],"MOQ":'+str(MOQ)+'},'
        PrePush=PrePush+oitsData+Items
    if(oits==0):
            print("The data has been pushing over!")
            return oits
    pushData='['+PrePush+']'
    LoadTime=time.strftime('%H:%M:%S',time.localtime(time.time()))
    L="Load json end time:"+str(LoadTime)
    print(L)
    pushData=pushData.replace("'",'"')
    
#    print(pushData)

   # fileHandle = open ('log/desc'+str(times),'w') 
   # fileHandle.write(pushData) 
   # fileHandle.close() 
    pushing(pushURL,pushData,times,oits[7])
    #close the MysqlConnect
    Mydb1.close()
    Mydb2.close()
    db.close()

 
#get the SessionKey
def getSession(password):
    url="http://192.168.100.201:9998/Security/RegisterUser/json/T0030" 
    pwd=password  
    m = hashlib.md5(pwd.encode(encoding='utf-8'))  
    PassKey=m.hexdigest()
    #print(m.hexdigest())  
    #print(PassKey)
    #data=urllib.parse.urlencode(password) 

    PassKey=PassKey.encode(encoding='utf-8', errors = 'strict')
    #创建请求对象  
    req=urllib.request.Request(url,PassKey) 
    #获得服务器返回的数据  
    try:  
        response=urllib.request.urlopen(req) 
    except urllib.error.HTTPError as e:  
        print('Error code:',e.code)   
        return 404
    except urllib.error.URLError as e:  
        print('Reason',e.reason)  
        return 404
    sessionKey=response.read().decode('utf-8')
    SesKey=json.loads(sessionKey)
   # print(SesKey)
    #print(SesKey["Result"])
    return SesKey["Result"]

#Manuplate the Database
def MysqlConnect(DataBase):
    try:
          cnx = mysql.connector.connect(user='root', password='123456',host='192.168.100.201',database=DataBase)
         # cnx = mysql.connector.connect(user='admin001', password='admin001',host='192.168.100.58',database=DataBase)
    except mysql.connector.Error as err:
          if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
              print("Something is wrong with your user name or password")
          elif err.errno == errorcode.ER_BAD_DB_ERROR:
              print("Database does not exists")
          else:
              print(err)
    else:
        return cnx 
def pushing(URL,PushData,times,CardName):
    PushData=PushData.encode(encoding='utf-8', errors = 'strict')
    #创建请求对象  
    req1=urllib.request.Request(URL,PushData) 
    #获得服务器返回的数据  
    try:  
        response1=urllib.request.urlopen(req1) 
    except urllib.error.HTTPError as e:  
        print('Error code:',e.code)   
        return 404
    except urllib.error.URLError as e:  
        print('Reason',e.reason)  
        return 404
    id=response1.read().decode('utf-8') 
    id=id.replace('"','\\"')
    id=id.replace("'","\\'")
    #connect mysql for recording the log
    db1=MysqlConnect('pushLog')
    log=db1.cursor()
#    logQuery="Insert into offline(LOG,times,CardName) VALUES('"+id+"',"+str(times)+",'"+str(CardName)+"')"
   # logQuery="UPDATE mouser SET LOG='"+id+"' WHERE times="+str(times)
    logQuery="Insert into rs(LOG,times) VALUES('"+id+"',"+str(times)+")"
    log.execute(logQuery)
    log.close()
    db1.close()
